package com.clstu.datasource;

import com.clstu.utils.Actor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 演示apache-DBUtils的使用,由于resultSet的直接使用不太方便(必须维持connection连接,占用资源)
 * 于是需要保存结果后赶紧关闭resultSet以便后续使用
 * 把resultSet结果集装在ArrayList返回
 */
public class DBUtilsUse{

    @Test//用 DBUtils + 基于Druid的JDBCUtils 查询多个记录结果
    public void testQueryMany() throws SQLException {
        Connection connection = JdbcUtilsByDruid.getConnection();
        //要使用工具库,先引入dbutils-jar 文件
        QueryRunner queryRunner = new QueryRunner();//queryRunner就已经可以帮我们执行sql语句了
        String sql = "select name,birthday from actor where id < ?";//也可以只返回需要的列
        //最后的参数是可变参数,用于给sql语句的?赋值 , 执行之后就能得到结果集转换为的ArrayList了
        //需要传入Actor.class的原因就是底层用到了反射,这样才能得到我们需要的对象集合
        List<Actor> actors = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 5);//做了一堆事
        /** //query()方法做的事:
         * private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
         *         if (conn == null) {
         *             throw new SQLException("Null connection");
         *         } else if (sql == null) {
         *             if (closeConn) {
         *                 this.close(conn);
         *             }
         *
         *             throw new SQLException("Null SQL statement");
         *         } else if (rsh == null) {
         *             if (closeConn) {
         *                 this.close(conn);
         *             }
         *
         *             throw new SQLException("Null ResultSetHandler");
         *         } else {
         *             PreparedStatement stmt = null;//这里开始就跟土方法封装结果集差不多的
         *             ResultSet rs = null;
         *             Object result = null;
         *
         *             try {
         *                 stmt = this.prepareStatement(conn, sql);//得到statement
         *                 this.fillStatement(stmt, params); //填充?
         *                 rs = this.wrap(stmt.executeQuery());//得到结果集
         *                 result = rsh.handle(rs);//封装到ArrayList中
         *             } catch (SQLException var33) {
         *                 this.rethrow(var33, sql, params);
         *             } finally {
         *                 try {
         *                     this.close(rs);//关闭结果集
         *                 } finally {
         *                     this.close(stmt);//关闭Statement
         *                     if (closeConn) {
         *                         this.close(conn);//好像还帮我们把连接放回到了连接池
         *                     }
         *
         *                 }
         *             }
         *
         *             return result;
         *         }
         *     }
         */
        //可以提前把连接放回连接池了
        JdbcUtilsByDruid.close(null,null,connection);//其他的资源都是由query()方法产生并且帮我们关闭了的,我们只需要关闭连接即可
        for (Actor actor : actors) {//遍历输出结果
            System.out.println(actor);
        }
    }

    @Test//测试 apache-dbUtils + Druid 查询单个结果
    public void testSingle() throws SQLException {
        Connection connection = JdbcUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();//得到这个对象就可以用它执行查询语句了
        String sql="select * from actor where id = ?";
        //变化 BeanListHandler ----> BeanHandler
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 3);
        System.out.println(actor);
        JdbcUtilsByDruid.close(null,null,connection);
    }

    @Test//测试 apache-dbUtils+ druid 查询单行单列清况
    public void testScalar() throws SQLException {
        Connection connection = JdbcUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql="select name from actor where id = ?";
        //变化  BeanHandler ----> ScalarHandler
        Object o = queryRunner.query(connection, sql, new ScalarHandler<>(), 3);
        System.out.println(o);
        JdbcUtilsByDruid.close(null,null,connection);
    }

    @Test//测试apache-dbUtils + druid的dlm(操作,insert update delete)
    public void testDML() throws SQLException {
        Connection connection = JdbcUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
//        String sql ="update actor set name = ? where id = ?";//改
        String sql ="delete from actor where id = ?";//删
//        String sql = "insert into actor values (?,?,?,?)";//增
        int i = queryRunner.update(connection, sql, 2);
        System.out.println(i>0?"执行成功":"执行没有影响到表");
        JdbcUtilsByDruid.close(null,null,connection);
    }
}
